## Inserindo dados

```{r}
#| message: false
#| warning: false
library(RSQLite)
library(tidyverse)
```

```{r}
if(!"discoCopy.db" %in% list.files("dados/")){
  file.copy("dados/disco.db","dados/discoCopy.db")} 
```

```{r}
db <- dbConnect(SQLite(),"dados/discoCopy.db")
```

## Inserindo uma tabela

```{r}
# Neste momento, existem as seguintes tabelas em disco.db:
dbListTables(db)
```

```{r}
# A sintaxe para criar uma tabela vazia, no SQLite, é através do comando CREATE TABLE nome (col1 tipo, col2 tipo2, ...)
dbExecute(db,"CREATE TABLE instruments
              (AlbumId INTEGER,
               TrackId INTEGER,
               ElectricGuitar INTEGER,
               Singer INTEGER,
               Trumpet INTEGER)")
```

## Removendo uma tabela

```{r}
dbListFields(db, 'instruments')
```

```{r}
#Você pode remover uma tabela usando o comando DROP TABLE nome:
dbExecute(db,"DROP TABLE instruments")
```

```{r}
dbListTables(db)
```

#Em geral, DROP TABLE é perigoso. Se você abrir seu servidor, pode ficarsujeito aos chamados "injection attacks"!

## Um ligeiro parêntese: Bestpractices

```{r}
aname = "Gilberto Gil"
sql = paste0("SELECT ArtistId FROM artists ","WHERE Name = '", aname,"'")
aId = dbGetQuery(db, sql)
sql = paste('SELECT Title FROM albums','WHERE ArtistId =', aId)
dbGetQuery(db, sql)
```

```{r}
#Um usuário malicioso pode inserir algo como
aname <- "Gilberto Gil'; DROP TABLE 'albums"
#E destruir seu banco de dados! 
```

## Best practices

```{r}
#O RSQLite oferece funções que executam queries com segurança

sql = paste("SELECT ArtistId FROM artists","WHERE Name = ?")
query <- dbSendQuery(db, sql)
dbBind(query, list("Gilberto Gil"))
aId <- dbFetch(query)
dbClearResult(query)
# Segundo passo interno, não deve causar problema
sql = paste('SELECT Title FROM albums','WHERE ArtistId =', aId)
dbGetQuery(db, sql)
```

## Incluindo linhas numa tabela

```{r}
#Voltando ao caso de instrumentos, suponha que eu tenha criado a tabela"instruments", e quero completá-la com alguma informação. Uma maneira defazê-lo é usando o comando INSERT INTO tabela VALUES (...)

dbExecute(db,"CREATE TABLE instruments
              (AlbumId INTEGER,
               TrackId INTEGER,
               ElectricGuitar INTEGER,
               Singer INTEGER,
               Trumpet INTEGER)")
dbListFields(db,'instruments')
```

```{r}
# Eu Tu Eles: AlbumId 85,
sql = paste('SELECT TrackId, Name FROM tracks','WHERE AlbumId = 85')
dbGetQuery(db, sql) %>% head
```

## Incluindo linhas numa tabela

```{r}
dbExecute(db,"INSERT INTO instruments
          VALUES ('85','1075', 0, 1, 0),
          ('85','1078', 0, 1, 0); ")
```

```{r}
dbGetQuery(db,"SELECT * FROM instruments")
```

## Inserindo uma tabela diretamente

#O data.frame mtcars é um exemplo famoso de data frame no R. Eu vouincluí-lo no nosso banco de dados:

```{r}
dbWriteTable(db,"mtcars", mtcars)
```

```{r}
dbListTables(db)
```

```{r}
#Note que o atributo rownames (marcas dos carros) foi perdido! Mas há um parâmetro row.names em dbWriteTable.

dbGetQuery(db,
"SELECT * FROM mtcars") %>% head(3)
```

## Inserindo uma tabela diretamente:append

#O parâmetro append concatena uma tabela nova a dados existentes. Por exemplo:

```{r}
theAvgCar <- mtcars %>%
summarise_all(function(x) round(mean(x), 2))
theAvgCar
```

```{r}
dbWriteTable(db,"mtcars", theAvgCar, append = TRUE)
dbGetQuery(db,"SELECT * FROM mtcars") %>% tail(3)
```

## Inserindo uma tabela diretamente:overwrite

#O parâmetro overwrite sobrescreve a tabela

```{r}
dbWriteTable(db,"mtcars", mtcars, overwrite = TRUE)
dbGetQuery(db,"SELECT * FROM mtcars") %>% tail(3)
```

## Lendo chunks

#Em certo sentido, dbGetQuery() é um atalho para dbSendQuery() seguidode dbFetch() (e dbClearResult()). Uma vantagem de usar a sintaxe mais longa é que podemos ler dados em chunks:

```{r}
res <- dbSendQuery(db,
"SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
      chunk <- dbFetch(res, n = 5)
      print(nrow(chunk))
}
```

```{r}
#O exemplo acima só guarda o último chunk, e não é muito eficiente... mas pode ser suficiente se você estiver #guardando os resultados com dbWriteTable e append = TRUE.
dbClearResult(res)
```

## Fechando conexões

```{r}
dbDisconnect(db)
if("discoCopy.db" %in% list.files("dados/")){
file.remove("dados/discoCopy.db")
}
```

## Criando sua base de dados

```{r}
airports <- read_csv("dados/airports.csv", col_types = "cccccdd")
airlines <- read_csv("dados/airlines.csv", col_types = "cc")

air <- dbConnect(SQLite(), dbname="dados/air.db")
dbWriteTable(air, name = "airports", airports)
dbWriteTable(air, name = "airlines", airlines)

dbListTables(air)

```

## Criando sua base de dados

#Também pode usar a função copy_to(conn, df) do dplyr! A sintaxe é parecida.

```{r}
dbDisconnect(air)
if("air.db" %in% list.files("dados/")){
   file.remove("dados/air.db")
}
```

## Breve introdução ao dbplyr

#O pacote dbplyr estende algumas funcionalidades do dplyr a dados que estão armazenados em um bancos de dados externo.

```{r}
#| message: false
#| warning: false
library(RSQLite)
library(tidyverse)
library(dbplyr)
```

```{r}
db <- dbConnect(SQLite(),
"dados/disco.db") 
tracks <- tbl(db,"tracks") 
tracks %>% head(3)
```

## Verbos do dplyr disponíveis:

```{r}
meanTracks <- tracks %>%
  group_by(AlbumId) %>%
summarise(AvLen = mean(Milliseconds, na.rm = TRUE),
          AvCost = mean(UnitPrice, na.rm = TRUE))
meanTracks
```

## ...mas secretamente, são comandosde SQLite!

```{r}
meanTracks %>% show_query()
```

## Consulta, de fato

#Quando decidir o que precisa -\> usar o comando collect()

```{r}
mT <- meanTracks %>% collect()
mT
```

```{r}
dbDisconnect(db)
```
